package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar2;
import com.bw.bean.Sugar3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT\n" +
            "c.chapter_name name,\n" +
            "count(*) num,\n" +
            "sum(position_sec) sumtime,\n" +
            "count(u.user_id) rnum,\n" +
            "sum(position_sec)/count(u.user_id) avgtime\n" +
            "FROM user_chapter_process u\n" +
            "INNER JOIN chapter_info c ON u.chapter_id=c.id\n" +
            "GROUP BY c.chapter_name")
    List<Sugar2> get1();

    @Select("SELECT a.lv name, count(*) value FROM\n" +
            "(SELECT id,\n" +
            "case \n" +
            "WHEN score<60 THEN '[0,60)'\n" +
            "WHEN score>=60 and score<80 THEN '[60,80)'\n" +
            "WHEN score>=80 and score<90 THEN '[80,90)'\n" +
            "WHEN score>=90 and score<95 THEN '[90,95)'\n" +
            "WHEN score>=95 and score<=100 THEN '[95,100]'\n" +
            "end lv\n" +
            "FROM test_exam)a\n" +
            "GROUP BY a.lv")
    List<Sugar> get2();

    @Select("SELECT b.name,sum(o.final_amount) value FROM order_info o\n" +
            "INNER JOIN base_province b ON o.province_id=b.id\n" +
            "GROUP BY b.name")
    List<Sugar> get3();

    @Select("SELECT sum(final_amount) FROM order_info\n")
    double get4();

    @Select("SELECT HOUR(create_time) name,count(*) value FROM user_chapter_process  GROUP BY HOUR(create_time)\n")
    List<Sugar> get5();

    @Select("SELECT \n" +
            "b.source_site name,\n" +
            "sum(o.final_amount) value,\n" +
            "count(DISTINCT o.user_id) ynum,\n" +
            "count(o.id) dnum\n" +
            " FROM order_info o\n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN course_info c ON d.course_id=c.id \n" +
            "INNER JOIN chapter_info ch ON c.id=ch.course_id \n" +
            "INNER JOIN video_info v ON ch.id=v.chapter_id \n" +
            "INNER JOIN base_source b ON v.video_source_id=b.id\n" +
            "GROUP BY b.source_site")
    List<Sugar3> get6();


    @Select("SELECT\n" +
            "(SELECT count(*) FROM order_info WHERE DATE_FORMAT(create_time,'%Y-%m-%d')='2021-04-23')-\n" +
            "(SELECT count(*) FROM order_info WHERE DATE_FORMAT(create_time,'%Y-%m-%d')='2021-04-22') VALUE")
    double get7();

    @Select("SELECT c.course_name name,sum(o.final_amount) value FROM order_info o\n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN course_info c ON d.course_id=c.id\n" +
            "GROUP BY c.course_name ")
    List<Sugar> get8();
}
